package com.oocl.crmweb.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.oocl.crmweb.dao.CustomerDao;
import com.oocl.crmweb.pojo.Customer;
import com.oocl.crmweb.util.DBUtil;
import com.oocl.crmweb.util.FavsStringUtil;

public class CustomerDaoImpl implements CustomerDao {

	@Override
	public int add(Customer c) {
		String sql = "insert into customers(id, cname, sal, sex, birth, favs) values(customer_id_seq.nextval, ?, ?, ?, ?, ?)";
		Connection conn = null;
		PreparedStatement pst = null;
		int count = 0;
		
		try {
			conn = DBUtil.createConnection();
			pst = conn.prepareStatement(sql);
			
			pst.setString(1, c.getCname());
			pst.setDouble(2, c.getSal());
			pst.setBoolean(3, c.isSex());
			pst.setDate(4, new java.sql.Date(c.getBirth().getTime()));
			pst.setString(5, FavsStringUtil.favsToString(c.getFavs()));
			
			count = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, null);
		}
		return count;
	}

	@Override
	public List<Customer> findAll() {
		List<Customer> customerList = new ArrayList<>();
		String sql = "select * from customers";
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		
		try {
			conn = DBUtil.createConnection();
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				Customer c = new Customer(rs.getInt("id"),
						rs.getString("cname"), rs.getDouble("sal"),
						rs.getBoolean("sex"), new java.util.Date(rs.getDate("birth").getTime()),
						FavsStringUtil.stringToFavs(rs.getString("favs")));
				customerList.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, rs);
		}
		return customerList;
	}
	
	@Override
	public Customer load(Integer id) {
		String sql = "select * from customers where id=?";
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		Customer c = null;
		
		try {
			conn = DBUtil.createConnection();
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			if (rs.next()) {
				c = new Customer(rs.getInt("id"), rs.getString("cname"),
						rs.getDouble("sal"), rs.getBoolean("sex"),
						new java.util.Date(rs.getDate("birth").getTime()),
						FavsStringUtil.stringToFavs(rs.getString("favs")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, rs);
		}
		return c;
	}

	@Override
	public int update(Customer c) {
		String sql = "update customers set cname=?, sal=?, sex=?, birth=?, favs=? where id=?";
		Connection conn = null;
		PreparedStatement pst = null;
		int count = 0;
		
		try {
			conn = DBUtil.createConnection();
			pst = conn.prepareStatement(sql);
			
			pst.setString(1, c.getCname());
			pst.setDouble(2, c.getSal());
			pst.setBoolean(3, c.isSex());
			pst.setDate(4, new java.sql.Date(c.getBirth().getTime()));
			pst.setString(5, FavsStringUtil.favsToString(c.getFavs()));
			pst.setInt(6, c.getId());
			
			count = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, null);
		}
		return count;
	}

	@Override
	public int delete(Integer id) {
		String sql = "delete from customers where id=?";
		Connection conn = null;
		PreparedStatement pst = null;
		int count = 0;
		
		try {
			conn = DBUtil.createConnection();
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			count = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, null);
		}
		return count;
	}

}
